package com.bungarus.group.jdbc;

import com.bungarus.group.cachestore.CacheStore;
import com.bungarus.group.cachestore.StoreColumn;
import com.bungarus.group.model.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;


/**
 * Created by tang on 2018/12/2.
 */
@Component
public class GroupMemberManager {
    Logger logger = LoggerFactory.getLogger(GroupMemberManager.class);

    private static final String DEFAULT_SORT_PROPERTY = "mname";
    private static final int INSERT_BATCH_SIZE = 50;

    private JdbcTemplate jdbcTemplate;

    private CacheStore cacheStore;
    private GroupMemberCacheTransformer groupMemberCacheTransformer;
    private MemberGroupCacheTransformer memberGroupCacheTransformer;

    @Autowired
    public GroupMemberManager(JdbcTemplate jdbcTemplate,
                              CacheStore cacheStore,
                              GroupMemberCacheTransformer groupMemberCacheTransformer,
                              MemberGroupCacheTransformer memberGroupCacheTransformer) {
        this.jdbcTemplate = jdbcTemplate;
        this.cacheStore = cacheStore;
        this.groupMemberCacheTransformer = groupMemberCacheTransformer;
        this.memberGroupCacheTransformer = memberGroupCacheTransformer;
    }

    @Transactional(readOnly = true, propagation = Propagation.SUPPORTS)
    public List<Long> findGroupIdsMemberBelongsTo(GroupMember member) {
        Pageable page = PageRequest.of(0, Integer.MAX_VALUE);
        List<Long> result = cacheStore.items(
                CacheStore.ofUniqueId(String.valueOf(member.getTenantId()), String.valueOf(member.getMid())),
                page, memberGroupCacheTransformer);

        if(null == result || result.size() == 0) {
            String query = "select gid from t_member_group where mid = ? and tenantId = ? order by gid";
            result = jdbcTemplate.query(query, new Object[]{member.getMid(), member.getTenantId()},
                    (resultSet, rowNum) -> {
                        return resultSet.getLong("gid");
                    });
        }
        return result;
    }

    @Transactional(readOnly = true, propagation = Propagation.SUPPORTS)
    public List<Long> findGroupIdsMemberBelongsToPageable(GroupMember member, Pageable page) {
        if(null == page) {
            throw new IllegalArgumentException("page not be null.");
        }
        List<Long> result = cacheStore.items(
                CacheStore.ofUniqueId(String.valueOf(member.getTenantId()), String.valueOf(member.getMid())),
                page, memberGroupCacheTransformer);

        if(null == result || result.size() == 0) {
            StringBuilder query = new StringBuilder("select gid from t_member_group where mid = ? and tenantId = ? order by gid");
            query.append(" limit ");
            query.append(page.getOffset());
            query.append(" , ");
            query.append(page.getPageSize());
            result = jdbcTemplate.query(query.toString(), new Object[]{member.getMid(), member.getTenantId()},
                    (resultSet, rowNum) -> {
                        return resultSet.getLong("gid");
                    });
        }
        return result;
    }

    @Transactional
    public void deleteByGid(GroupMember group) {
        //Delete group in cache first
        Pageable page = PageRequest.of(0, INSERT_BATCH_SIZE);
        List<Long> mids = null;
        do {
            if(null != mids) {
                mids.clear();
            }
            mids = cacheStore.items(
                    CacheStore.ofUniqueId(String.valueOf(group.getTenantId()), String.valueOf(group.getGid())),
                    page, groupMemberCacheTransformer);
            if(mids.size() > 0) {
                List<GroupMember> gms = mids
                        .stream()
                        .map(mid -> {
                            GroupMember gm = new GroupMember();
                            gm.setTenantId(group.getTenantId());
                            gm.setGid(group.getGid());
                            gm.setMid(mid);
                            return gm;
                        })
                        .collect(Collectors.toList());
                cacheStore.deleteItems(memberGroupCacheTransformer, gms);
                page = page.next();
            }
        }
        while (mids.size() > 0);

        cacheStore.delete(CacheStore.ofUniqueId(
                String.valueOf(group.getTenantId()),
                String.valueOf(group.getGid())), groupMemberCacheTransformer);

        //Delete group in db
        String[] delSqls = {"delete from t_group_member where gid=? and tenantId=?",
                "delete from t_member_group where gid=? and tenantId=?"};
        for(String delSql: delSqls) {
            jdbcTemplate.execute(delSql, (PreparedStatement preparedStatement) -> {
                preparedStatement.setLong(1, group.getGid());
                preparedStatement.setLong(2, group.getTenantId());
                return preparedStatement.execute();
            });
        }
    }

    @Transactional
    public GroupMember save(final GroupMember gm) {
        if(memberExistsInGroup(gm)) {
            return gm;
        }
        String[] insertSqls = {"insert into t_group_member (gid, mid, gname, mname, tenantId) values (?,?,?,?,?)",
                        "insert into t_member_group (gid, mid, gname, mname, tenantId) values (?,?,?,?,?)"};
        try {
            for(String insertSql: insertSqls) {
                jdbcTemplate.execute(insertSql, (PreparedStatement preparedStatement) -> {
                    preparedStatement.setLong(1, gm.getGid());
                    preparedStatement.setLong(2, gm.getMid());
                    preparedStatement.setString(3, gm.getgName());
                    preparedStatement.setString(4, gm.getmName());
                    preparedStatement.setLong(5, gm.getTenantId());

                    return preparedStatement.execute();
                });
            }
            List<GroupMember> gms = new ArrayList<>();
            gms.add(gm);
            cacheStore.save(groupMemberCacheTransformer, gms);
            cacheStore.save(memberGroupCacheTransformer, gms);
        }
        catch (Exception e) {
            e.printStackTrace();
            return null;
        }
        return gm;
    }

    private boolean memberExistsInGroup(final GroupMember gm) {
        return cacheStore.itemExists(
                        CacheStore.ofUniqueId(String.valueOf(gm.getTenantId()), String.valueOf(gm.getGid())),
                        String.valueOf(gm.getMid()), groupMemberCacheTransformer) &&
                cacheStore.itemExists(
                        CacheStore.ofUniqueId(String.valueOf(gm.getTenantId()), String.valueOf(gm.getMid())),
                        String.valueOf(gm.getGid()), memberGroupCacheTransformer);
    }

    @Transactional
    public void saveAll(List<GroupMember> gms) {
        String[] insertSqls = {"insert into t_group_member (gid, mid, gname, mname, tenantId) values (?,?,?,?,?)",
                        "insert into t_member_group (gid, mid, gname, mname, tenantId) values (?,?,?,?,?)"};
        for(String insertSql: insertSqls) {
            for (int i = 0; i < gms.size(); i += INSERT_BATCH_SIZE) {
                final List<GroupMember> batchList = gms.subList(i, i + INSERT_BATCH_SIZE > gms.size() ? gms.size() : INSERT_BATCH_SIZE + i);
                jdbcTemplate.batchUpdate(insertSql, new BatchPreparedStatementSetter() {
                    @Override
                    public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
                        GroupMember gm = batchList.get(i);
                        preparedStatement.setLong(1, gm.getGid());
                        preparedStatement.setLong(2, gm.getMid());
                        preparedStatement.setString(3, gm.getgName());
                        preparedStatement.setString(4, gm.getmName());
                        preparedStatement.setLong(5, gm.getTenantId());
                    }

                    @Override
                    public int getBatchSize() {
                        return batchList.size();
                    }
                });
            }
        }
        cacheStore.save(groupMemberCacheTransformer, gms);
        cacheStore.saveList(memberGroupCacheTransformer, gms);
    }

    @Transactional(readOnly = true, propagation = Propagation.SUPPORTS)
    public int totalCount(long tenantId) {
        String querySql = "select count(1) as size from t_group_member where tenantId=?";

        return jdbcTemplate.queryForObject(querySql, new Object[]{tenantId}, (resultSet, i) -> {
            return resultSet.getInt("size");
        });
    }

    @Transactional(readOnly = true, propagation = Propagation.SUPPORTS)
    public int totalMemberCountOfGroup(GroupMember gm) {
        long count = cacheStore.itemCount(
                CacheStore.ofUniqueId(String.valueOf(gm.getTenantId()), String.valueOf(gm.getGid())),
                groupMemberCacheTransformer);
        if(count <= 0) {
            String querySql = "select count(1) as size from t_group_member where gid=? and tenantId=?";

            return jdbcTemplate.queryForObject(querySql, new Object[]{gm.getGid(), gm.getTenantId()}, (resultSet, i) -> {
                return resultSet.getInt("size");
            });
        }
        return (int)count;
    }

    @Transactional(readOnly = true, propagation = Propagation.SUPPORTS)
    public List<Member> findMembers(GroupMember gm, Pageable page) {
        StringBuilder sb =
                new StringBuilder("select mid, mname, tenantId from t_group_member where gid=? and tenantId=?");
        if(null != page) {
            if(page.getSort().isSorted()) {
                sb.append(" order by ");
                sb.append(StringUtils.replace(page.getSort().toString(), ":", ""));
            }
            sb.append(" limit ");
            sb.append(page.getOffset());
            sb.append(", ");
            sb.append(page.getPageSize());
        }
        String querySql = sb.toString();
        return jdbcTemplate.query(querySql, new Object[]{gm.getGid(), gm.getTenantId()},
                (resultSet, rowNum) -> {
                    Member member = new Member();
                    member.setId(resultSet.getLong("mid"));
                    member.setName(resultSet.getString("mname"));
                    member.setTenantId(resultSet.getLong("tenantId"));
                    return member;
                });
    }

    @Transactional(readOnly = true, propagation = Propagation.SUPPORTS)
    public List<Member> findMembers(GroupMember gm, int offset, int size) {
        Pageable page = PageRequest.of(offset, size, Sort.by(DEFAULT_SORT_PROPERTY));
        return findMembers(gm, page);
    }

    @Transactional
    public void deleteMemberOfTenant(GroupMember member) {
        //Delete the member in cache
        int page = 0;
        List<Long> gids = null;
        do {
            if(null != gids) {
                gids.clear();
            }
            //Get group ids
            gids = this.findGroupIdsMemberBelongsToPageable(member, PageRequest.of(page, 50));
            List<Long> ggid = gids;
            List<GroupMember> gmList = ggid.stream()
                    .map(gid -> {
                        GroupMember gm = new GroupMember();
                        gm.setMid(member.getMid());
                        gm.setTenantId(member.getTenantId());
                        gm.setGid(gid);
                        return gm;
                    })
                    .collect(Collectors.toList());
            cacheStore.deleteItems(groupMemberCacheTransformer, gmList);
            page++;
        } while (gids.size() > 0);
        cacheStore.delete(
                CacheStore.ofUniqueId(String.valueOf(member.getTenantId()), String.valueOf(member.getMid())),
                memberGroupCacheTransformer);

        //Delete the member in db
        String[] delSqls = {"delete from t_group_member where mid=? and tenantId=?",
                "delete from t_member_group where mid=? and tenantId=?"};
        for(String delSql: delSqls) {
            jdbcTemplate.execute(delSql, (PreparedStatement preparedStatement) -> {
                preparedStatement.setLong(1, member.getMid());
                preparedStatement.setLong(2, member.getTenantId());
                return preparedStatement.execute();
            });
        }
    }

    @Transactional
    public void deleteMemberInGroup(GroupMember gm) {
        //Delete the member in cache
        StoreColumn column = StoreColumn
                .newBuilder()
                .name(String.valueOf(gm.getMid()))
                .value(String.valueOf(gm.getMid()))
                .build();
        cacheStore.deleteItem(
                CacheStore.ofUniqueId(String.valueOf(gm.getTenantId()), String.valueOf(gm.getGid())),
                column,
                groupMemberCacheTransformer
        );

        column = StoreColumn
                .newBuilder()
                .name(String.valueOf(gm.getGid()))
                .value(String.valueOf(gm.getGid()))
                .build();
        cacheStore.deleteItem(
                CacheStore.ofUniqueId(String.valueOf(gm.getTenantId()), String.valueOf(gm.getMid())),
                column,
                memberGroupCacheTransformer
        );

        //Delete the member in db
        String[] delSqls = {"delete from t_group_member where mid=? and gid=? and tenantId=?",
                "delete from t_member_group where mid=? and gid=? and tenantId=?"};
        for(String delSql: delSqls) {
            jdbcTemplate.execute(delSql, (PreparedStatement preparedStatement) -> {
                preparedStatement.setLong(1, gm.getMid());
                preparedStatement.setLong(2, gm.getGid());
                preparedStatement.setLong(3, gm.getTenantId());
                return preparedStatement.execute();
            });
        }
    }

    @Transactional(readOnly = true, propagation = Propagation.SUPPORTS)
    public List<Group> findGroupsMemberBelongsTo(GroupMember member) {
        return findGroupsMemberBelongsTo(member, Sort.by("gid"));
    }

    @Transactional(readOnly = true, propagation = Propagation.SUPPORTS)
    public List<Group> findGroupsMemberBelongsTo(GroupMember member, Sort sort) {
        if(null == sort) {
            throw new IllegalArgumentException("Sort cannot be null.");
        }
        StringBuilder sb =
                new StringBuilder("select gid, gname from t_member_group where mid = ? and tenantId = ?");

        if(sort.isSorted()) {
            sb.append(" order by ");
            sb.append(StringUtils.replace(sort.toString(), ":", ""));
        }

        String querySql = sb.toString();

        List<Group> results = jdbcTemplate.query(querySql, new Object[]{member.getMid(), member.getTenantId()},
                (resultSet, rowNum) -> {
                    Group group = Group.newBuilder()
                            .id(resultSet.getLong("gid"))
                            .name(resultSet.getString("gname"))
                            .tenant(member.getTenantId())
                            .build();
                    return group;
                });
        return results;
    }

    @Transactional(readOnly = true, propagation = Propagation.SUPPORTS)
    public List<Group> findGroupsMemberBelongsTo(GroupMember member, Pageable page) {
        if(null == page) {
            throw new IllegalArgumentException("page cannot be null.");
        }
        StringBuilder sb =
                new StringBuilder("select gid, gname from t_member_group where mid = ? and tenantId = ?");

        if(page.getSort().isSorted()) {
            sb.append(" order by ");
            sb.append(StringUtils.replace(page.getSort().toString(), ":", ""));
        }
        sb.append(" limit ");
        sb.append(page.getOffset());
        sb.append(", ");
        sb.append(page.getPageSize());

        String querySql = sb.toString();

        List<Group> results = jdbcTemplate.query(querySql, new Object[]{member.getMid(), member.getTenantId()},
                (resultSet, rowNum) -> {
                    Group group = Group.newBuilder()
                            .id(resultSet.getLong("gid"))
                            .name(resultSet.getString("gname"))
                            .tenant(member.getTenantId())
                            .build();
                    return group;
                });
        return results;
    }

    @Transactional(readOnly = true, propagation = Propagation.SUPPORTS)
    public int totalCountOfGroupsMemberBelongsTo(long mid, long tid) {
        long total = cacheStore.itemCount(
                CacheStore.ofUniqueId(String.valueOf(tid), String.valueOf(mid)),
                memberGroupCacheTransformer
        );
        if(total <= 0) {
            String querySql = "select count(1) as size from t_member_group where mid=? and tenantId=?";

            return jdbcTemplate.queryForObject(querySql, new Object[]{mid, tid}, (resultSet, i) -> {
                return resultSet.getInt("size");
            });
        }
        return (int)total;
    }
}
